const express = require('express')
const Message = require('../util/Message')
const {Subject, Class, QueryTypes, sequelize, Course, Op, Homework, HomeworkLog, User} = require('../util/sequelize')
const {formatTime} = require('../util/formatTime')
const router = express.Router()

router.get('/getSubjectAndClass', async (req, res) => {
    const subject = await Subject.findAll({
        attributes: [['id', 'value'], ['name', 'label']]
    })
    const classes = await Class.findAll({
        attributes: [['id', 'value'], ['name', 'label']],
        order: [
            ['value', 'ASC']
        ]
    })
    res.send(Message.sendSuccess([subject, classes]))
})
router.post('/addClass', async (req, res) => {
    const {name} = req.body
    const [classes, boolean] = await Class.findOrCreate({
        where: {
            name
        }
    })
    if (boolean) {
        res.send(Message.sendSuccess(null, '新增成功'))
    } else {
        res.status(500).send(Message.sendFail('该班级已存在，请重新输入'))
    }
})
router.post('/updateSubject', async (req, res) => {
    const {id, name} = req.body
    Subject.update({
        name
    }, {
        where: {
            id
        }
    }).then((value, reject) => {
        res.send(Message.sendSuccess(null, '更新成功'))
    }).catch(err => {
        res.status(500).send(Message.sendFail('该“学科名称”已经存在，请更换'))
    })
})
router.post('/updateClass', async (req, res) => {
    const {id, name} = req.body
    Class.update({
        name
    }, {
        where: {
            id
        }
    }).then((value, reject) => {
        res.send(Message.sendSuccess(null, '更新成功'))
    }).catch(err => {
        res.status(500).send(Message.sendFail('该“班级名称”已经存在，请更换'))
    })
})
router.post('/deleteClass', async (req, res) => {
    const {id} = req.body
    await Class.destroy({
        where: { id }
    })
    res.send(Message.sendSuccess(null, '删除成功'))
})
router.post('/addSubject', async (req, res) => {
    const {name} = req.body
    const [classes, boolean] = await Subject.findOrCreate({
        where: {
            name
        }
    })
    if (boolean) {
        res.send(Message.sendSuccess(null, '新增成功'))
    } else {
        res.status(500).send(Message.sendFail('该学科已存在，请重新输入'))
    }
})
router.post('/deleteSubject', async (req, res) => {
    const {id} = req.body
    const courseId = await Course.findAll({
        attributes: ['id'],
        where: {subject_id: id}
    })
    Promise.all(JSON.parse(JSON.stringify(courseId)).map(item => deleteCourse(item.id))).then(async () => {
        await Subject.destroy({
            where: { id }
        })
    })
    res.send(Message.sendSuccess(null, '删除成功'))
})
router.post('/getCourse', async (req, res) => {
    const {name, subject_id, class_id, createdAt, currentPage, pageSize} = req.body
    delete req.body.currentPage
    delete req.body.pageSize
    // 因为这里用了 teacher_id，所以我查到的是该老师才有课程，学生是进不来管理中心的
    let sql = `select c.id as id, c.name, c.video, c.filePath, c.img, c.description, s.name as subject, s.id as subject_id, cl.name as class, cl.id as class_id from course c, subject s, class cl where c.class_id = cl.id and c.subject_id = s.id and c.teacher_id = ${req.auth.id}`
    for (const key in req.body) {
        const val = req.body[key]
        if (val) {
            if (key === 'name') {
                sql += ` and c.${key} like '%${val}%'`
            } else if (key === 'createdAt') {
                sql += ` and c.${key} > '${formatTime('yy-MM-dd HH: mm: ss', new Date(val))}'`
            } else {
                sql += ` and c.${key} = ${val}`
            }
        }
    }
    const total = await sequelize.query(sql, {
        type: QueryTypes.SELECT
    })
    sql += ` order by id desc limit ${(currentPage - 1) * pageSize}, ${pageSize}`
    const course = await sequelize.query(sql, {
        type: QueryTypes.SELECT
    })
    res.send(Message.sendSuccess({
        course,
        total: JSON.parse(JSON.stringify(total)).length
    }))
})
router.post('/getClass', async (req, res) => {
    const {currentPage, pageSize, name} = req.body
    const sql = `select id value, name label from class where name like '%${name}%' order by id desc limit ${(currentPage - 1) * pageSize}, ${pageSize}`
    const classes = await sequelize.query(sql, {
        type: QueryTypes.SELECT
    })
    const total = await sequelize.query(`select id value, name label from class where name like '%${name}%'`, {
        type: QueryTypes.SELECT
    })
    res.send(Message.sendSuccess({class: classes, total: JSON.parse(JSON.stringify(total)).length}))
})
router.post('/getSubject', async (req, res) => {
    const {currentPage, pageSize, name} = req.body
    const sql = `select id value, name label from subject where name like '%${name}%' order by id desc limit ${(currentPage - 1) * pageSize}, ${pageSize}`
    const subject = await sequelize.query(sql, {
        type: QueryTypes.SELECT
    })
    const total = await sequelize.query(`select id value, name label from subject where name like '%${name}%'`, {
        type: QueryTypes.SELECT
    })
    res.send(Message.sendSuccess({subject, total: JSON.parse(JSON.stringify(total)).length}))
})
router.post('/saveOrUpdateCourse', async (req, res) => {
    const {id, courseName: name, video, previewUrl: img, description, filePath, subject_id, class_id} = req.body
    if (id) {
        Course.update({
            name,
            img,
            description,
            subject_id,
            class_id,
            video,
            filePath
        }, {
            where: {
                id
            }
        }).then((value, reject) => {
            res.send(Message.sendSuccess(null, '更新成功'))
        }).catch(err => {
            res.status(500).send(Message.sendFail('该“课程名称”已经存在，请更换'))
        })
    } else {
        await Course.create({name, video, img, description, filePath, subject_id, class_id, teacher_id: req.auth.id})
        res.send(Message.sendSuccess(null, '创建成功'))
    }
})
router.post('/deleteCourse', async (req, res) => {
    /*
    * 删除课程、以及对应的作业、以及对应的作业完成记录
    * 注意删除时，要先删除没有依赖的那些表，即要先删除外键的表
    * */
    const {id} = req.body
    await deleteCourse(id)
    res.send(Message.sendSuccess(null, '删除成功'))
})
router.post('/saveOrUpdateHomeWork', async (req, res) => {
    const {id, title, answer, startTime, endTime, course_id, isProgram, file, img} = req.body
    if (id) {
        Homework.update({
            title, answer, startTime, endTime, course_id, isProgram, file, img
        }, {
            where: {
                id
            }
        }).then((value, reject) => {
            res.send(Message.sendSuccess(null, '更新成功'))
        }).catch(err => {
            res.status(500).send(Message.sendFail('该“作业名称”已经存在，请更换'))
        })
    } else {
        await Homework.create({
            title, answer, startTime, endTime, course_id, isProgram, file, img
        })
        res.send(Message.sendSuccess(null, '创建成功'))
    }
})
router.post('/getTeacherHomework', async (req, res) => {
    /*
    * 先看老师教哪些课，再看这些课有哪些作业
    * */
    const {pageSize, currentPage} = req.body
    delete req.body.pageSize
    delete req.body.currentPage
    let sql = `select h.id, h.isProgram, h.file, h.title, h.img as previewUrl, h.answer, h.startTime, h.endTime, c.name as subject, c.class_id from homework h, course c where c.teacher_id = ${req.auth.id} and h.course_id = c.id`
    for (const key in req.body) {
        const val = req.body[key]
        if (val) {
            if (key === 'name') {
                sql += ` and h.title like '%${val}%'`
            } else if (key === 'createdAt') {
                sql += ` and h.${key} > '${formatTime('yy-MM-dd HH: mm: ss', new Date(val))}'`
            } else {
                sql += ` and h.${key} = ${val}`
            }
        }
    }
    sql += ` order by id desc limit ${(currentPage - 1) * pageSize}, ${pageSize}`
    const homework = await sequelize.query(sql, {
        type: QueryTypes.SELECT
    })
    const total = await sequelize.query(`select count(*) total from homework h, course c where c.teacher_id = ${req.auth.id} and h.course_id = c.id`, {
        type: QueryTypes.SELECT
    })
    res.send(Message.sendSuccess({homework, total: total[0].total}))
})
router.get('/deleteHomework', async (req, res) => {
    const {id} = req.query
    await HomeworkLog.destroy({
        where: {
            homework_id: id
        }
    })
    await Homework.destroy({
        where: {id}
    })
    res.send(Message.sendSuccess(null, '删除成功'))
})
router.get('/getHomeworkProgress', async(req, res) => {
    const {id, class_id} = req.query
    const student = await User.findAll({
        attributes: ['id', 'nickname'],
        where: {class_id}
    })
    let finishStudent = await sequelize.query(`select u.nickname, h.id, h.student_id, h.studentAnswer, h.score, h.teacherRemark, h.file from homeworkLog h, user u where h.homework_id=${id} and h.student_id = u.id`, {
        type: QueryTypes.SELECT
    })
    finishStudent = JSON.parse(JSON.stringify(finishStudent))
    const finishArr = finishStudent.map(item => item.student_id)
    const checkArr = finishStudent.filter(item => item.score).map(item => item.student_id)
    res.send(Message.sendSuccess({
        student, // 该班级的学生信息
        finishStudent, // 该班级提交了作业的学生
        finishArr,  // 提交了作业的学生的id
        finishNumber: finishArr.length, // 提交了作业的学生的数量
        checkArr // 已批阅的学生的id
    }))
})
router.post('/updateHomeworkLog', async (req, res) => {
    const {id, score, teacherRemark} = req.body
    HomeworkLog.update({score, teacherRemark}, {
        where: {
            id
        }
    }).then((value, reject) => {
        res.send(Message.sendSuccess(null, '批阅成功'))
    }).catch(err => {
        res.status(500).send(Message.sendFail())
    })
})

async function deleteCourse(id) {
    const homeworkId = await Homework.findAll({
        attributes: ['id'],
        where: {course_id: id}
    })
    await HomeworkLog.destroy({
        where: {
            homework_id: {
                [Op.or]: JSON.parse(JSON.stringify(homeworkId)).map(item => item.id)
            }
        }
    })
    await Homework.destroy({
        where: {course_id: id}
    })
    await Course.destroy({
        where: {id}
    })
}

module.exports = router
